package com.apestech.framework.sql;

import java.util.ArrayList;
import java.util.Date;
import java.util.UUID;

import com.apestech.framework.db.Format;
import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.sql.modul.FromList;
import com.apestech.framework.sql.modul.MultiDB;
import com.apestech.framework.util.callback.SpiltStrCallback;
import com.apestech.framework.xml.plugin.modul.GridHead;
import com.apestech.framework.xml.sql.modul.QueryhTw;
import com.apestech.framework.xml.sql.SqlNodeVar;
import com.apestech.framework.util.DateUtil;
import com.apestech.framework.util.StringUtil;

public class MultiDataBase extends StringUtil{
	private String dbType;
	public MultiDataBase(String dbType){
		this.dbType=dbType;
	}
	
	/**
	 * 获取时间戳
	 * @return
	 */
	public String getTimeStamp(){
		switch(this.dbType){
        	case "ORACLE":
        		return "TO_CHAR(SYSTIMESTAMP, 'YYYYMMDD.HH24MISS.FF3')";
        	case "MYSQL":
        		return "FROM_UNIXTIME(UNIX_TIMESTAMP(NOW), '%Y%m%d.%H%i%S')";
        	default:
        		return "null";
		}
	}

	/**
	 * 时间戳字段转义，放SELECT部分使用
	 * @param field
	 * @param alias
	 * @return
	 */
	public String toTimeStamp(String field, String alias){
		switch(this.dbType){
			case "ORACLE":
				return "TO_CHAR("+alias+field+", 'YYYYMMDD.HH24MISS.FF3') AS "+field;
			case "MYSQL":
				return "FROM_UNIXTIME(UNIX_TIMESTAMP("+alias+field+"), '%Y%m%d.%H%i%S') AS "+field;
			default:
				return "null";
		}
	}
	
	/**
	 * 获取数据库系统常量时间变量
	 * @return
	 */
	public String getSysDateTime() {
		switch(this.dbType){
	    	case "ORACLE":
	    		return "SYSDATE";
	    	case "MYSQL":
	    		return "DATE";
	    	default:
	    		return "null";
		}
	}
	
	/**
	 * 内部函数，获取格式化的时间字段
	 * @param date
	 * @return
	 */
	private String DateToSql(String date){
		if (date == null) return null;
		date=date.trim().toUpperCase();
		switch(this.dbType){
	    	case "ORACLE":
	    		if(!"SYSDATE".equals(date)){
	    			return  "'" + date + "'";
	    		}
	    	case "MYSQL":
	    		if(!"DATE".equals(date) || !"NOW".equals(date)){
	    			return "'" + date + "'";
	    		}
	    	default:
	    		return "'" + date + "'";
		}
	}
	
	/**
	 * 获取数据库的日期格式
	 * @return
	 */
	private String getDateFormat(){
		switch(this.dbType){
	    	case "ORACLE":
	    		return "'YYYY-MM-DD'";
	    	case "MYSQL":
	    		return "'%Y-%m-%d'";
	    	default:
	    		return "null";
		}
	}
	
	/**
	 * 获取数据库的时间格式
	 * @return
	 */
	private String getDateTimeFormat(){
		switch(this.dbType){
	    	case "ORACLE":
	    		return "'YYYY-MM-DD HH24:MI:SS'";
	    	case "MYSQL":
	    		return "'%Y-%m-%d %H:%i:%S'";
	    	default:
	    		return "null";
		}
	}
	
	/**
	 * 获取数据库里的日期字符
	 * @param date
	 * @return
	 */
	public String getDateStr(String date) {
		date=DateToSql(date);
		if (date == null) return "null";
		date=date.trim().toUpperCase();
		String format=getDateFormat();
		switch(this.dbType){
	    	case "ORACLE":
	    		return "TO_CHAR("+date+","+format+")";
	    	case "MYSQL":
	    		return "FROM_UNIXTIME(UNIX_TIMESTAMP("+date+"),"+format+")";
	    	default:
	    		return "null";
		}
	}
		
	/**
	 * 获取数据库里的时间字符
	 * @param date
	 * @return
	 */
	public String getDateTimeStr(String date) {
		date=DateToSql(date);
		if (date == null) return "null";
		String format=getDateTimeFormat();
		switch(this.dbType){
	    	case "ORACLE":
	    		return "TO_CHAR("+date+","+format+")";
	    	case "MYSQL":
	    		return "FROM_UNIXTIME(UNIX_TIMESTAMP("+date+"),"+format+")";
	    	default:
	    		return "";
		}
	}
	
	/**
	 * 获取数据库日期字串
	 * @param date
	 * @return
	 */
	public String getDateStr(Date date) {
		if (date == null) return "null";
		return getDateStr(DateUtil.dateTimeToStr(date));
	}
	
	/**
	 * 获取数据库时间字串
	 * @param date
	 * @return
	 */
	public String getDateTimeStr(Date date) {
		if (date == null) return "null";
		return getDateTimeStr(DateUtil.dateTimeToStr(date));
	}
	
	//STR_TO_DATE('2017-10-16 15:30:28','%Y-%m-%d %H:%i:%s')
	//DATE_FORMAT(DATE,'%Y-%m-%d %H:%i:%s')
	//"FROM_UNIXTIME(UNIX_TIMESTAMP(NOW), '%Y%m%d.%H%i%S')";
	//SELECT UNIX_TIMESTAMP('2010-03-01 00:00:00')
	//SELECT FROM_UNIXTIME(1267372800)
		
	/**
	 * 获取数据库日期格式
	 * @param date
	 * @return
	 */
	public String getDate(String date) {
		date=DateToSql(date);
		if (date == null) return "null";
		String format=getDateFormat();
		switch(this.dbType){
	    	case "ORACLE":
	    		return "TO_DATE(TO_CHAR("+date+", "+format+"), "+format+")";
	    	case "MYSQL":
	    		return "UNIX_TIMESTAMP("+date+")";
	    	default:
	    		return "null";
		}
	}
	
	/**
	 * 获取数据库日期格式
	 * @param date
	 * @return
	 */
	public String getDate(Date date) {
		if (date == null) return "null";
		return getDate(DateUtil.dateTimeToStr(date));
	}
	
	/**
	 * 获取数据库当前日期的字串格式
	 * @return
	 */
	public String getSysDateStr(){
		return getDateStr(getSysDateTime());
	}
	
	/**
	 * 获取数据库当前时间的字串格式
	 * @return
	 */
	public String getSysDateTimeStr(){
		return getDateTimeStr(getSysDateTime());
	}
	
	/**
	 * 获取数据库时间格式
	 * @param date
	 * @return
	 */
	public String getDateTime(String date) {
		date=DateToSql(date);
		if (date == null) return "null";
		String format=getDateTimeFormat();
		switch(this.dbType){
	    	case "ORACLE":
	    		return "TO_DATE("+date+", "+format+")";
	    	case "MYSQL":
	    		return "UNIX_TIMESTAMP("+date+")";
	    	default:
	    		return "null";
		}
	}
	
	/**
	 * 获取数据库时间格式
	 * @param date
	 * @return
	 */
	public String getDateTime(Date date) {
		if (date == null) return "null";
		return getDateTime(DateUtil.dateTimeToStr(date));
	}

	public String getDateToNumber(String field){
		return "TO_NUMBER(TO_CHAR("+field+",'YYYYMMDDHH24MISS'))";  //14
	}

	public String getTimeStampToNumber(String field){
		return "TO_NUMBER(TO_CHAR("+field+",'YYYYMMDDHH24MISSFF3'))";  //17
	}

	public String getNumberToTimeStamp(String field){
		return "TO_TIMESTAMP(TO_CHAR("+field+"),'YYYYMMDDHH24MISSFF3')";
	}

	public String getNumberToDate(String field){
		return "TO_DATE(TO_CHAR("+field+"),'YYYYMMDDHH24MISS')";
	}

	public String getTimeStampToStr(String field){
		return "TO_CHAR("+field+",'YYYY-MM-DD HH24:MI:SS.FF3')";  //23
	}

	public String getDateToStr(String field){
		return "TO_CHAR("+field+",'YYYY-MM-DD HH24:MI:SS')";  //19
	}

	public String getStrToTimeStamp(String field){
		return "TO_TIMESTAMP("+field+",'YYYY-MM-DD HH24:MI:SS.FF3')";
	}

	public String getTimeStampToDate(String field){
		return "TO_DATE(SUBSTR(TO_CHAR("+field+",'YYYY-MM-DD HH24:MI:SS.FF3'),0,19),'YYYY-MM-DD HH24:MI:SS')";
	}

	public String getDateToTimeStamp(String field){
		return "TO_TIMESTAMP(TO_CHAR("+field+",'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS.FF3')";
	}

	public String getTimeStampToNumberToDate(String field){
		return "TO_DATE(SUBSTR(TO_CHAR("+field+"),0,14),'YYYYMMDDHH24MISS')";
	}

	public String getDateToNumberToTimeStamp(String field){
		return "TO_TIMESTAMP(TO_CHAR("+field+"),'YYYYMMDDHH24MISSFF3')";
	}

	public String getTimeStampToStrToDate(String field){
		return "TO_DATE(SUBSTR("+field+",0,19),'YYYY-MM-DD HH24:MI:SS')";
	}

	public String getSumSql(String sql, Format format, QueryhTw htw){
		if(format==null) return null;
		int len=format.getSize();
		ArrayList<String> klist=format.getKeylist();
		String hql="";
		for(int i=0; i<len; i++){
			String key=klist.get(i);
			Format.FormatField field=format.getFieldById(key);
			if(field!=null && field.getFieldCount()!=null){
				if(!"".equals(hql)){
					hql=hql+",";
				}
				if(htw!=null){
					if(htw.getGridHead().getPivotmap().get(key)!=null){
						ArrayList<String> plist= (ArrayList<String>) htw.getGridHead().getPivotmap().get(key);
						int plen=plist.size();
						for(int j=0; j<plen; j++){
							if(j>0) hql=hql+",";
							hql=setSumFieldSql(hql, field, plist.get(j));
						}
					} else hql=setSumFieldSql(hql, field, key);
				} else hql=setSumFieldSql(hql, field, key);
			}
		}
		if("".equals(hql)) return null;
		else return "SELECT "+hql+",COUNT(1) SYS_TOTAL FROM ("+sql+")";
	}

	private String setSumFieldSql(String hql, Format.FormatField field, String key){
		if(field.getMath()!=null){
			if(field.isSingle()) hql=hql+field.getFieldCount()+"(MATH_VALUE("+key+",'"+field.getMath()+"',"+field.getScale()+")) AS "+key;
			else hql=hql+"MATH_VALUE("+field.getFieldCount()+"("+key+"),'"+field.getMath()+"',"+field.getScale()+") AS "+key;
		} else hql=hql+field.getFieldCount()+"("+key+") AS "+key;
		return hql;
	}

	public String getExecFormatSql(ArrayList<String> klist, String sql, Format format){
		if(format==null) return sql;
		boolean bcount=false;
		String hql="";
		int len=klist.size();
		for(int i=0; i<len; i++){
			String key=klist.get(i);
			if(!"".equals(hql)){
				hql=hql+",";
			}
			boolean flag=true;
			Format.FormatField field=format.getFieldById(key);
			if(field!=null){
				if(field.getMath()!=null){
					hql=hql+"MATH_VALUE("+key+",'"+field.getMath()+"',"+field.getScale()+") AS "+key;
					flag=false;
					bcount=true;
				}
			}
			if(flag){
				hql=hql+key;
			}
		}
		if(!bcount) return sql;
		return "SELECT "+hql+" FROM ("+sql+")";
	}

	/**
	 * 获取分页SQL
	 * @param sql
	 * @return
	 */
	public String getPageSql(String sql, boolean fast, boolean forwhile) {
		String hql = sql;
		String sPageHead = "";
		String sPagetail = "";
		if ("ORACLE".equals(this.dbType)) {
			if(!fast) sPageHead = "SELECT PAGE.* FROM(SELECT TAB.*,ROW_NUMBER() OVER (ORDER BY ROWNUM) AS NUM FROM (";
			else sPageHead = "SELECT PAGE.* FROM(SELECT TAB.*,ROWNUM AS NUM FROM (";
			if(forwhile){
				sPagetail = ") TAB WHERE ROWNUM <= "+SqlNodeVar.getMaxCount()+" ) PAGE  WHERE PAGE.NUM >= 1 ";
			} else {
				sPagetail = ") TAB WHERE ROWNUM <= ? ) PAGE  WHERE PAGE.NUM >= ? ";
			}
			//sPagetail = ") TAB WHERE ROWNUM <= #{iePage} ) PAGE  WHERE PAGE.NUM >= #{isPage} ";
		} else if ("MYSQL".equals(this.dbType)) {
			sPagetail = " LIMIT ?, ? ";
			//sPagetail = " LIMIT #{isPage}, #{iePage} ";
		}
		hql = sPageHead + hql + sPagetail;
		return hql;
	}


	public String getPagePrintSql(String sql, boolean fast, int start, int end) {
		String hql = sql;
		String sPageHead = "";
		String sPagetail = "";
		if ("ORACLE".equals(this.dbType)) {
			if(!fast) sPageHead = "SELECT PAGE.* FROM(SELECT TAB.*,ROW_NUMBER() OVER (ORDER BY ROWNUM) AS NUM FROM (";
			else sPageHead = "SELECT PAGE.* FROM(SELECT TAB.*,ROWNUM AS NUM FROM (";
			sPagetail = ") TAB WHERE ROWNUM <= "+end+" ) PAGE  WHERE PAGE.NUM >= "+start;
		} else if ("MYSQL".equals(this.dbType)) {
			sPagetail = " LIMIT "+start+", "+end;
		}
		hql = sPageHead + hql + sPagetail;
		return hql;
	}

	public JSONArray getPageParam(JSONArray param, int start, int end){
		JSONArray list=new JSONArray();
		list.addAll(param);
		if ("ORACLE".equals(this.dbType)) {
			list.add(addParam(end));
			list.add(addParam(start));
		} else if ("MYSQL".equals(this.dbType)) {
			list.add(addParam(start));
			list.add(addParam(end));
		}
		return list;
	}

	private JSONObject addParam(int value){
		JSONObject pmap=new JSONObject();
		pmap.put("value", value);
		pmap.put("type", "BIG");
		return pmap;
	}
	
	/**
	 * 获取COUNT子串
	 * @param sSql
	 * @return
	 */
	public String getCountSql(String sSql) {
		String hql = sSql;
		hql = "SELECT COUNT(*) AS CT FROM (" + hql + " )";
		return hql;
	}
	
	/**
	 * 获取SUM子串
	 * @param sSql
	 * @param sSumField
	 * @return
	 */
	public String getSumSql(String sSql,String sSumField) {
		String hql = sSql;
		sSumField=sSumField+";";
		String[] list=sSumField.split(";");
		String fql = "";
		int ilen=list.length;
		for(int i=0;i<ilen;i++){
			if(!list[i].equals("")){
				if(!fql.equals("")){
					fql=fql+",";
				}
				fql=fql+"SUM("+list[i]+") AS "+list[i];
			}
		}
		hql="SELECT "+fql+" FROM (" + sSql + " )";
		return hql;
	}
	
	public String getStringConcat(String... params){
		String s="";
		int len=params.length;
		for(int i=0; i<len; i++){
			if ("ORACLE".equals(this.dbType)) {
				if(!"".equals(s)){
					s=s+"||";
				}
			} else {
				if(!"".equals(s)){
					s=s+",";
				}
			}
			s=s+params[i];
		}
		if ("MYSQL".equals(this.dbType) && !"".equals(s)) {
			s="CONCAT("+s+")";
		}
		return s;
	}

	public String getCreateCol(String id, String type, boolean notNull, String defaultValue){
		if(id==null) return null;
		if(type==null) return null;
		String f="";
		f=id+" "+getFieldType(type);
		if(defaultValue!=null){
			f=f+" default "+defaultValue+" ";
		}
		if(notNull){
			f=f+" NOT NULL";
		}
		//Id int primary key auto_increment
		//Name varchar(18)
		return f;
	}

	private String getFieldType(String type){
		//目前暂以ORALCE类型为主，将来可根据数据类型拆分
		String t="";
		//type=type.toUpperCase();
		if ("ORACLE".equals(this.dbType)) {
			return type;
		} else {
			return type;
		}
	}

	public String getComment(String name, String tableName, String id){
		if ("ORACLE".equals(this.dbType)) {
			if(id==null){
				return "COMMENT ON TABLE "+tableName+" IS '"+name+"'";
			} else {
				return "COMMENT ON COLUMN "+tableName+"."+id+" is '"+name+"'";
			}
		} else if ("MYSQL".equals(this.dbType)) {
			if(id==null){
				return "ALTER TABLE "+tableName+" COMMENT '"+name+"'";
			} else {
				return "ALTER TABLE "+tableName+" MODIFY COLUMN "+id+" COMMENT '"+name+"'";
			}
		}
		return null;
	}

	public String getPrimarySql(String tabName, String primaryFields){
		String iname="PK_"+tabName;
		if(iname.length()>16){
			iname= "PK_"+ getRandomString(14);
		}
		return "ALTER TABLE "+tabName+" ADD CONSTRAINT "+iname+" PRIMARY KEY ("+primaryFields+")";
	}

	public String getForeignSql(String tabName, String id, String foreignTab, String foreignFields){
		String sid=id;
		String stab="FK_"+tabName+"_"+id;
		if(stab.length()>16) stab="FK"+getRandomString(14);
		if(foreignFields.indexOf(",")>=0) sid=foreignFields;
		return "ALTER TABLE "+tabName+
				" ADD CONSTRAINT "+stab+
				" FOREIGN KEY ("+sid+") REFERENCES "+foreignTab+"("+foreignFields+")";
	}

	public String getUniqueIndexSql(String tabName, String sufname, String fields){
		String iname="I_"+tabName+"_"+sufname;
		if(iname.length()>16){
			iname= "I_"+ getRandomString(14);
		}
		return "ALTER TABLE "+tabName+" ADD CONSTRAINT "+iname+" UNIQUE ("+fields+")";
	}

	public String getMaxCountWhere(boolean xls){
		if(!xls){
			if(SqlNodeVar.getMaxCount()<=0) return "";
			if ("ORACLE".equals(this.dbType)) return " AND ROWNUM<="+ SqlNodeVar.getMaxCount();
			else return "";
		} else {
			if(SqlNodeVar.getMaxXlsCount()<=0) return "";
			if ("ORACLE".equals(this.dbType)) return " AND ROWNUM<="+ SqlNodeVar.getMaxXlsCount();
			else return "";
		}

	}

	public String getAddPrimaryKey(String tableName, String key){
		return "ALTER TABLE "+tableName+" ADD CONSTRAINT PK_"+tableName+" PRIMARY KEY ("+key+")";
	}

	public String getDropTable(String tableName){
		return "DROP TABLE "+tableName;
	}

	public String getDropSequence(String seqName){
		return " DROP SEQUENCE "+seqName;
	}

	public String getDeleteTable(String tableName){
		return "DELETE FROM "+tableName;
	}

	public  String getTreeChild(String tree, String idField, String parentField, String field, String alias, Object value) throws Exception {
		return getTree(tree, idField, parentField, field, alias, value, true);
	}

	public  String getTreeParent(String tree, String idField, String parentField, String field, String alias, Object value) throws Exception {
		return getTree(tree, idField, parentField, field, alias, value, false);
	}

	private String getTree(String tree, String idField, String parentField, String field, String alias, Object value, boolean child) throws Exception {
		if(value==null) return "";
		if(tree==null || "".equals(tree)) throw new Exception("请检查：["+field+"]字段并非为树形结构字段，不允许使用parent, tree查询条件！");
		String f=alias+field;
		String sql="SELECT 1 FROM "+tree+" TREE WHERE TREE."+idField+"="+f;
		ArrayList list;
		if(value instanceof ArrayList){
			list= (ArrayList) value;
		} else {
			list=new ArrayList();
			list.add(value);
		}
		//-------------*--------------//
        sql="";
        int len=list.size();
		for(int i=0; i<len; i++){
		    if(!"".equals(sql)) sql=sql+" OR ";
		    if(child) sql=sql+alias+field+" LIKE '"+list.get(i)+"%' ";
		    else sql=sql+"'"+list.get(i)+"' LIKE "+alias+field+"||'%' ";
        }
		return sql="("+sql+")";
        //-------------*--------------//
//		return sql+treeCondition("TREE", idField, parentField, list, child);
	}

	private String treeCondition(String alias, String idField, String parentField, ArrayList list, boolean child){
		if(list==null) return "";
		int len=list.size();
		if(len==0) return "";
		String id=idField;
		String pid=parentField;
		if(alias!=null && !"".equals(alias)){
			id=alias+"."+id;
			pid=alias+"."+pid;
		}
		String value="";
		for(int i=0; i<len; i++){
			Object obj=list.get(i);
			if(!"".equals(value)) value=value+",";
			if(obj instanceof String){
				value=value+"'"+obj+"'";
			} else{
				value=value+obj;
			}
		}
		if(child) return treeChild(id, pid, value);
		else return treeParent(id, pid, value);
	}

	private String treeChild(String id, String pid, String value){ //x like '0101%'
		return " START WITH "+id+" IN ("+value+") CONNECT BY "+pid+"=PRIOR "+id+" ";
	}

	private String treeParent(String id, String pid, String value){ //'0101' like x||'%'
		return " START WITH "+id+" IN("+value+") CONNECT BY PRIOR "+pid+"="+id+" ";
	}

	public String preTree(String tree, String idField, String field, String alias, Object value) throws Exception {
		if (value == null) return "";
		if (tree == null || "".equals(tree)) throw new Exception("请检查：["+field+"]字段并非为树形结构字段，不允许使用parent, tree查询条件！");
		String f = field;
		if(!(alias==null && "".equals(alias))) f=alias+"."+f;
		String sql = "SELECT 1 FROM " + tree + " TREE WHERE TREE." + idField + "=" + f;
		sql=sql+ " START WITH ";
		return sql;
	}

	public String sufTree(String idField, String parentField){
		String id="TREE."+idField;
		String pid="TREE."+parentField;
		return " CONNECT BY "+pid+"=PRIOR "+id+" ";
	}

	public String htwLine(QueryhTw htw, String sql) throws Exception {
		String nsql="";
		if(htw.getDataField()==null || "".equals(htw.getDataField())) throw new Exception("hTw结点下的dataField属性不能为空，请补全！");
		if(htw.getTable()!=null){
			nsql="SELECT "+htw.getIdField()+","+htw.getNameField()+" FROM "+htw.getTable()+" WHERE "+htw.getIdField()+" IN (";
			nsql=nsql+" SELECT "+htw.getDataField()+" FROM ("+sql+")";
			nsql=nsql+")";
		} else {
			nsql=" SELECT DISTINCT "+htw.getDataField()+" FROM ("+sql+")";
		}
		return nsql;
	}

	public String htw(QueryhTw htw, String sql) throws Exception {
		if(htw==null) return sql;
		String nsql="SELECT * FROM ("+sql+" ) P PIVOT (";
		nsql=nsql+getPivotlist(htw)+" FOR "+htw.getDataField()+" IN (";
		nsql=nsql+getPivotHead(htw);
		nsql=nsql+"))";
		return nsql;
	}

	public String getHtwFormatSql(ArrayList<String> klist, String sql, QueryhTw htw, Format format){
		if(htw==null) return sql;
		boolean bcount=false;
		String group="";
		String hql="";
		int len=klist.size();
		for(int i=0; i<len; i++){
			String key=klist.get(i);
			if(!"".equals(hql) ){
				hql=hql+",";
			}
			boolean flag=true;
			if(flag){
				String pfield="";
				QueryhTw.Pivot pivot= (QueryhTw.Pivot) htw.getPivotlist().get(key);
				if(pivot!=null){
					pfield=pivot.getExpress()+"(";
					Format.FormatField field=format.getFieldById(key);
					if(field!=null){
						if(field.getMath()!=null){
							pfield=pfield+"MATH_VALUE("+key+",'"+field.getMath()+"',"+field.getScale()+") ";
							flag=false;
							bcount=true;
						}
					} else {
						if(pivot!=null){
							pfield=pfield+key;
							flag=false;
							bcount=true;
						}
					}
					pfield=pfield+")";
					hql=hql+pfield+" AS "+key;
				}
				htw.setPivotAlais(key, pfield);
			}

			if(flag){
				hql=hql+key;
				if(!"".equals(group)) group=group+",";
				group=group+key;
			}
		}
		if(!bcount) return sql;
		return "SELECT "+hql+" FROM ("+sql+") GROUP BY "+group;
	}

	private String getPivotlist(QueryhTw htw) throws Exception {
		if(htw.getPivotlist()==null) throw new Exception("pivot结点配置不可为空！");
		String p="";
		int len=htw.getPivotlist().size();
		ArrayList<String> klist=htw.getPivotlist().getKeylist();
		for(int i=0; i<len; i++){
			QueryhTw.Pivot pivot= (QueryhTw.Pivot) htw.getPivotlist().get(klist.get(i));
			if(!"".equals(p)) p=p+",";
			p=p+pivot.getAlais()+" AS "+pivot.getField();
		}
		return p;
	}

	private String getPivotHead(QueryhTw htw) throws Exception {
		if(htw.getHeadlist()==null) throw new Exception("当前动态列没有符合条件数据，请检查数据合法性再执行查询操作！");
		String h="";
		int len=htw.getHeadlist().size();
		for(int i=0; i<len; i++){
			GridHead.PivotHead head=htw.getHeadlist().get(i);
			if(!"".equals(h)) h=h+",";
			if(head.getValue() instanceof String){
				h=h+"'"+head.getValue()+"'";
				if(!"0".equals(leftStr((String) head.getValue(),1))) h=h+" AS "+head.getField();
				else h=h+" AS "+htw.getDataField()+"_"+head.getField();
			}
			else {
				h=h+head.getValue();
				h=h+" AS "+head.getField();
			}
		}
		return h;
	}

	public MultiDB.MutiField getMutiFieldWhere(FromList fromtab, String field, String alais) throws Exception {
		if(field==null) return null;
		int pos=field.indexOf(".");
		if(pos<0) return null;
		if(alais==null) alais="";
		if(!"".equals(alais) && alais.indexOf(".")<0) alais=alais+".";
		String from="";
		String where="";
		String[] list=field.split("\\.");
		int len=list.length;
		int j=1;
		Table table=null;
		if(len>0){
			int tlen=fromtab.size();
			for(int i=0; i<tlen; i++) {
				table = fromtab.getDictTable(fromtab.getTable(i));
				if(table.getfTab(list[0])!=null) break;
				else table=null;
			}
		}
		if(table==null) throw new Exception("中间字段ID["+list[0]+"]必须为外键字段");
		String lastfield=null;
		String lastalais=null;
		for(int i=0; i<len; i++){
			String key=list[i];
			String tabname=table.getfTab(key);
			String fid=table.getfId(key);
			if(i==0){
				where=where+" AND "+alais+key+"=FK"+j+"."+fid;
			} else{
				if(i==len-1){
					lastfield=key;
					lastalais="FK"+(j-1);
				} else {
					where=where+" AND FK"+(j-1)+"."+key+"=FK"+j+"."+fid;
				}
			}
			if(i<len-1){
				if(!"".equals(from)) from=from+",";
				from=from+tabname+" FK"+j;
				j++;
				table=fromtab.getDictTableAll(tabname);
				if(table==null) throw new Exception("中间字段ID["+key+"]必须为外键字段");
			}
		}
		if(!"".equals(from)){
			MultiDB.MutiField f=new MultiDB.MutiField(table, lastfield, "EXISTS (SELECT 1 FROM "+from+" WHERE 1=1 "+where, ")", lastalais);
			return f;
		}
		return null;
	}

	public String getOrderSql(String sql, String fields, String order) throws Exception {
		final String[] str = {""};
		if(order==null) order="";
		String finalOrder = order;
		execSplitStr(fields, ";", new SpiltStrCallback(){
			@Override
			public void exec(String id) throws Exception {
				if(!str[0].equals("")) str[0] = str[0] +",";
				str[0]=str[0]+id+" "+ finalOrder;
			}
		});
		String text="";
		if(!"".equals(str[0])) text=" ORDER BY "+str[0];
		if("".equals(text)) return sql;
		return "SELECT * FROM ("+sql+") "+text;
	}


}
